const Router = require('koa-router');
const router = new Router();
const {db, QueryTypes, } = require('../../models/db')
const fs = require("fs");
const tools = require("../../tools/tool");
const xlsx = require("node-xlsx");
const downurl = 'http://192.168.3.3:9998/'

// 表单设计保存
router.post('/design/saveForm', async ctx => {
    let formData = ctx.request.body.formData;

    let id = formData.id;
    if(!id) return ctx.err('未保存')
    let mod = 'design'
    let model = await db.models[mod].findOne({
        where: {
            id: id
        }
    })
    if(!model) return  ctx.err('查询失败')
    model.params = JSON.stringify(formData.params)
    await model.save()
    let par = formData.params;
    let fieldStr = ''
    let idstr = ``
    let tenantStr = ``
    let cby = ''
    let uby = ''
    let dby = ''
    let ctime = ''
    let utime = ''
    let dtime = ''
    let isDel = ''
    if(formData.params.showId) {
        idstr = `id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true,comment:\'表自增ID\'},`
    }
    if(formData.params.tenantId) {
        tenantStr = `tenantId: {type:DataTypes.INTEGER,allowNull:false,comment:'租户',  },`
    }
    if(formData.params.cby) {
        cby = 'cby: {type: DataTypes.INTEGER,comment:\'创建人\'},'
    }
    if(formData.params.uby) {
        uby = 'uby: {type: DataTypes.INTEGER,comment:\'更新人\'},'
    }
    if(formData.params.dby) {
        dby = 'dby: {type: DataTypes.INTEGER,comment:\'删除人\'},'
    }
    if(formData.params.ctime) {
        ctime = 'ctime: {type: DataTypes.STRING,comment:\'创建时间\'},'
    }
    if(formData.params.utime) {
        utime = 'utime: {type: DataTypes.STRING,comment:\'更新时间\'},'
    }
    if(formData.params.dtime) {
        dtime = 'dtime: {type: DataTypes.STRING,comment:\'删除时间\', },'
    }
    if(formData.params.softDel) {
        isDel = 'isDel: {type: DataTypes.INTEGER,comment:\'删除0|1\', },'
    }
    par.fields.map(row => {
        if(row.field && row.fieldType) {
            let dt = ''
            if(row.fieldType === 'text' || row.fieldType === 'date' || row.fieldType === 'datetime') {
                dt = 'STRING'
            }
            if(row.fieldType === 'int' || row.fieldType === 'boolean' || row.fieldType === 'number') {
                dt = 'INTEGER'
            }
            if(row.fieldType === 'decimal') {
                dt = 'DECIMAL(18,2)'
            }
            if(row.fieldType === 'virtual') {
                dt = 'VIRTUAL'
            }
            if(dt) {
                let str = `${row.field}:{type: DataTypes.${dt},comment:'${row.title}'}, \n`
                fieldStr = fieldStr + str
            }
        }
    })
    let tb = `const {DataTypes, db } = require("../db");
db.define('${par.mod}', {
${idstr}
${tenantStr}
${fieldStr}
${isDel}
${cby}
${uby}
${dby}
${ctime}
${utime}
${dtime}
}, {
    tableName: '${par.tableName}',
    timestamps: false
});`
    if(par.mod) {
        fs.writeFile('./models/tables/'+par.mod+'.js', tb, err => {
            if(err) {
            }
            else {
            }
        })
    }
    return ctx.suc('已保存')
})
// router.post('/design/del', async ctx => {
//     try {
//         let ids = ctx.request.body.ids;
//         let mod = ctx.request.body.mod;
//         let isTree = ctx.request.body?.isTree;
//         if (isTree) {
//             // 找出code 摸查子分类 并删除
//             for(let id in ids) {
//                 let codeModel = await db.models[mod].findOne({
//                     where: {
//                         id: ids[id]
//                     }
//                 })
//                 if(codeModel) {
//                     await db.models[mod].destroy({
//                         where: {
//                             code: {
//                                 [Op.like]: codeModel.code +'%'
//                             }
//                         }
//                     })
//                 }
//             }
//             ctx.suc('已删除 ' + ids.length + ' 条记录')
//         }
//         else {
//             await db.models[mod].destroy({
//                 where: {
//                     id: {
//                         [Op.in]: ids
//                     }
//                 }
//             })
//             ctx.suc('已删除 ' + ids.length + ' 条记录')
//         }
//     }
//     catch (error) {
//         ctx.err(error)
//     }
// })
// 直接通过sql语句查询
router.post('/design/sqlList', async ctx => {
    try {
        let sql = ctx.request.body.sql;
        if(sql) {
            let ret = await db.query(sql, {type: QueryTypes.SELECT});
            return ctx.suc('', ret)
        }
        ctx.suc('', [])
    }
    catch (error) {
        ctx.err(error)
    }
})
// 直接通过sql语句查询
router.post('/design/fileImport', async ctx => {
    try {
        const {file} = ctx.request.files
        if(file) {
            return ctx.suc(`${file.name}`)
        }
        else {
            return  ctx.err('读取文件失败')
        }
        return  ctx.suc('suc')
    } catch (e) {
        return ctx.err('err')
    }
})
router.post('/design/syncTable', async ctx => {
    // try {
    //     let id = ctx.request.body.id;
    //     if(!id) return ctx.err('未找到模型');
    //     let model = await db.models['models'].findOne({ where: {id: id} });
    //     if(!model) return ctx.err('未找到模型');
    //     // 查询数据库中是否有表
    //     let totalModel = await db.query(`SELECT count(*) total from pg_tables where tablename='${model.tablename}'`, {type: QueryTypes.SELECT, plain: true});
    //     if (parseInt(totalModel.total) !== 0 ) {
    //         let hasData = await db.models[model.tablename].count();
    //         if (hasData > 0) return ctx.err('已有数据，请手动更新数据库表');
    //     }
    //     await db.models[model.tablename].sync({force: true});
    //     ctx.suc('同步成功');
    // }
    // catch (error) {
    //     ctx.err(error)
    // }
})
router.post('/design/syncTableFile', async ctx => {
    try {
        let id = ctx.request.body.id;
        if(!id) return ctx.err('未找到模型');
        let model = await db.models['models'].findOne({where: {id: id}});
        if(!model) return ctx.err('未找到模型');
        let data = await combFile(model)
        ctx.suc('')
    }
    catch (error) {
        ctx.err(error)
    }
})
// router.post('/design/list', async ctx => {
//     try {
//         let ret = await page(ctx)
//         if(ret) {
//             return ctx.suc('', ret)
//         }
//         return ctx.err('查询失败')
//     }
//     catch (error) {
//         ctx.err(error)
//     }
// })
router.post('/design/exportFile', async ctx => {
    try {
        const list = [
            {
                name: '下载文件',
                data: [
                    ['名称一', '名称二', '名称三', 'ss'],
                    ['1', '2', '3'],
                    [null, '2', '3'],
                ]
            }
        ]
        return ctx.suc('suc')
    } catch (e) {

    }
})
router.post('/design/modToTable', async ctx => {
    try {
        let mod = ctx.request.body.mod
        await db.models[mod].sync({force: true})
        return ctx.suc('已保存')
    }
    catch (e) {
        return ctx.err(e)
    }
})
// 组装页面参数
router.post('/design/getParams', async ctx => {
    try {
        let body = ctx.request.body;
        let whichModel = body.whichModel;
        if(whichModel) {

        }

        ctx.suc('保存成功 ')
    }
    catch (error) {
        ctx.err(error)
    }
})
// 生成导入模板
router.post('/design/moban', async ctx => {
    try {
        const list = [
            {
                name: '下载文件',
                data: [
                    ['名称一', '名称二', '名称三', 'ss'],
                    ['1', '2', '3'],
                    [null, '2', '3'],
                ]
            }
        ]
        const mobanData = ctx.request.body.formData;
        let obj = {
            name: mobanData.title,
            data: []
        }
        let oneRow = []
        mobanData.columns.map(row => {
            if(row.excelin) {
                oneRow.push(row.title)
            }
        })
        obj.data.push(oneRow)
        if(obj.data.length > 0) {
            const fname = await exportOut(obj.name, [obj])
            return ctx.suc('正在导出', downurl + fname)
        }
        else {
            return ctx.err('无导出数据')
        }
    } catch (e) {

    }
})

// formcust d保存
router.post('/design/list', async ctx => {
    // let keyWord = body.keyWord ? body.keyWord : '';
    let data = await db.models['formCustV5'].findAll({
        attributes: ['id', 'fileName', 'name', 'pid', 'pidName'],
        order: [['name', 'asc']]
    })

    return ctx.suc('', data);
})
router.post('/design/getById', async ctx => {
    let it = await db.models['formCustV5'].findOne({
        where: {
            id: ctx.request.body.id
        }
    })
    return ctx.suc('', it);
})
router.post('/design/uppostdata', async ctx => {
    let a = await db.query(`select top 5000* from invoice where billType in('SR') and (postData='' or postData is null) `, {type: QueryTypes.SELECT})
    for (let i = 0; i < a.length; i++) {
        let invoice = await db.query(`select * from invoice_other where iid=${a[i].id}`,{type: QueryTypes.SELECT})
        //let invoice = await db.query(`select * from invoice_info where iid=${a[i].id}`,{type: QueryTypes.SELECT})
        let post = a[i]
        post.infos = invoice
        await db.models['invoice'].update({postData: JSON.stringify(post)}, {
            where: {
                id: a[i].id
            }
        })
    }
    return ctx.suc('suc')
})

router.post('/design/getBtnTemp', async ctx => {
    let ret = [
        {
            name: '工具栏',
            tpl: tools.obj2str({
                field: 'toolbar', type: 'toolbar', props: {
                    btnList: [
                        {
                            name: '新增', icon: 'fa fa-plus', click: () => {
                                _tool.openEdit({
                                    whichPage: 'storage',
                                    title: '仓库管理',
                                    refresh: () => {
                                        _this.getList()
                                    }
                                })
                            }
                        },
                    ],
                    tabList: [
                        {id: 'all', name: '全部'},
                        {id: 'draft', name: '草稿'},
                        {id: 'accounted', name: '已审核'},
                        {id: 'del', name: '已删除'},
                    ],
                    quick: true
                },
                inject: true,
                on: {
                    excelOut: (inject) => {
                        inject.$f.exec('grid', 'getList', 1)
                    },
                    search: (inject) => {
                        inject.$f.exec('grid', 'search')
                    },
                    changeTab: (inject, tab) => {
                        inject.$f.exec('grid', 'search')
                    }
                }
            })
        },
        {
            name: '明细表格',
            tpl: tools.obj2str({
                field: 'gridDetail', type: 'JGrid', props: {
                    showFooter: true,
                    showPage: false,
                    offsetHeight: '1',
                    dataList: [],
                    fields: [
                        {field: 'seq', type: 'seq', width: 30,headerAlign: 'center',visible:true},
                        {field: 'billNo', title: '票号', width: 130,headerAlign: 'center',visible:true},
                        {field: 'invName', title: '商品', width: 300,headerAlign: 'center',visible:true},
                        {field: 'locationName', title: '仓库', width: 100,headerAlign: 'center',align: 'center',visible:true},
                        {field: 'spec', title: '规格', width: 80,headerAlign: 'center',align: 'center',visible:true},
                        {field: 'unit', title: '单位', width: 80,headerAlign: 'center',align: 'center',visible:true},
                        {field: 'qty', title: '数量', width: 100, sum: true,align: 'right',headerAlign: 'center',visible:true},
                        {field: 'price', title: '单价', width: 100,align: 'right',headerAlign: 'center',visible:true,formatter: 'formatMoney',},
                        {field: 'amount', title: '金额', width: 100, sum: true,align: 'right',headerAlign: 'center',formatter: 'formatMoney',visible:true,},
                        {field: 'remark', title: '备注', width: 100,align: 'right',headerAlign: 'center',visible:true},
                    ]
                }
            })
        },
        {
            name: '工具栏按钮',
            tpl: tools.obj2str({
                field: 'toolbar', type: 'toolbar', props: {
                    btnList: [
                        {
                            name: '新增', icon: 'fa fa-plus', click: () => {
                                _tool.openEdit({
                                    whichPage: 'storage',
                                    title: '仓库管理',
                                    refresh: () => {
                                        _this.getList()
                                    }
                                })
                            }
                        },
                    ],
                    tabList: [
                        {id: 'all', name: '全部'},
                        {id: 'draft', name: '草稿'},
                        {id: 'accounted', name: '已审核'},
                        {id: 'del', name: '已删除'},
                    ],
                    quick: true
                },
                inject: true,
                on: {
                    excelOut: (inject) => {
                        inject.$f.exec('grid', 'getList', 1)
                    },
                    search: (inject) => {
                        inject.$f.exec('grid', 'search')
                    },
                    changeTab: (inject, tab) => {
                        inject.$f.exec('grid', 'search')
                    }
                }
            })
        },
        {
            name: '行按钮普通',
            tpl: tools.obj2str({
                title: '操作',width: 130,fixed: 'right',align: 'center',slots: {default: 'op'},
                btns: [
                    {
                        label: '编辑', click: (row) => {
                            _this.openEdit({
                                whichPage:  _this.$route.name,
                                title: '编辑' + row.name,
                                saveUrl: 'page/saveForm',
                                row: row,
                                refresh: () => {
                                    _this.getList()
                                }
                            })
                        }
                    },
                    {label: '删除',confirm: {msg: '是否删除 ?'},color:'#8a8f97',click:(row) => {
                            _tool.confirm('确定删除数据 ？', '删除提示').then(() => {
                                _tool.post('page/del', {
                                    whichPage:  _this.$route.name,
                                    id: row.id
                                }).then(ret => {
                                    if (ret.stat) {
                                        _this.getList()
                                    }
                                })
                            })
                        }}
                ]
            })
        },
        {
            name: '页面',
            tpl: `
            {
                rule: [{
                    type: 'searchBar',
                    field: 'search',
                    props: {
                        whichPage: _this.whichPage
                    }
                },
                    {
                        type: 'toolbar',
                        field: 'toolbar',
                        props: {
                            btnList: [{
                                code: 'add',
                                name: '新增',
                                title: '新增',
                                icon: 'Plus',
                                type: 'success',
                            }]
                        },
                        inject: true,
                        on: {
                            search: () => {
                                _this.getList()
                            },
                            excelOut: () => {
                                _this.getList(1)
                            },
                            btnClick: (inject, val) => {
                                inject.$f.exec('grid', 'openEdit', {
                                    whichPage: _this.whichPage,
                                    saveUrl: 'page/saveForm',
                                    title: '新增',
                                    height: 600,
                                    refresh: () => {
                                        _this.getList()
                                    }
                                })
                            }
                        }
                    },
                    {
                        field: 'grid',
                        type: 'JGrid',
                        props: {
                            whichPage: _this.whichPage,
                            offsetHeight: 1
                        },
                        inject: true,
                        on: {
                            rowBtn: (inject, btn, row) => {
                                _this.rowBtn(btn, row)
                            },
                        }
                    }]
            }
            `
        },
        {
            name: '页面bill',
            tpl: `
            {
                rule: [
                    {type: 'searchBar', field: 'search', props: {whichPage: _this.whichPage}},
                    {type: 'toolbar', field: 'toolbar', props: {
                      btnList: [
                        {
                          code: 'add', name: '新增', title: '新增',icon: 'Plus', type: 'success',
                          click: () => {
                            let route = _tool.getRoute(_this.whichPage.substring(0, _this.whichPage.length-1))
                            if(route) {
                              _global.addRoute(route)
                            }
                          }
                        },
                      ], 
                        tabList:
                          [
                            {id: 'all', name: '全部'},
                            {id: 'draft', name: '草稿'},
                            {id: 'accounted', name: '已审核'},
                            {id: 'del', name: '已删除'},
                          ]
                      },
                      on: {
                        search: () => {
                          _this.getList()
                        },
                        changeTab: () => {
                          _this.getList()
                        },
                        excelOut: () => {
                          _this.getList(1)
                        }
                      }
                    },
                    {field: 'grid', type: 'JGrid', props:{whichPage: _this.whichPage, offsetHeight: '0.7'},
                      inject: true,
                      on: {
                        cellClick: (inject, {row}) => {
                          _tool.post('bill/getInvoiceDetail',{billNo: row.billNo}).then(ret => {
                            ret.data.map(r => {
                              r.billNo = row.billNo
                            })
                            inject.$f.exec('detail', 'setData', 0, ret.data)
                          })
                        },
                        rowBtn: (inject, btn, row) => {
                          if(btn.label === '审核') {
                            _tool.post('bill/toAccounted', {
                              ids: [row.id],
                              billType: row.billType,
                              menuId: _this.whichPage,
                              transType: row.transType
                            }, true).then(ret => {
                              if (ret.stat) {
                                _this.getList()
                              }
                            })
                          }
                          if(btn.label === '退审') {
                            _tool.confirm('确定退审 ？', '退审提示').then(() => {
                              _tool.post('bill/toWasted', {
                                ids: [row.id],
                                billType: row.billType,
                                menuId: _this.whichPage,
                                transType: row.transType
                              }, true).then(ret => {
                                if (ret.stat) {
                                  _this.getList()
                                }
                              })
                            })
                          }
                          if(btn.label === '删除') {
                            _tool.confirm('确定删除票据 ？', '删除提示').then(() => {
                              _tool.post('bill/toDel', {
                                ids: [row.id],
                                billType: row.billType,
                                menuId: _this.whichPage,
                                transType: row.transType
                              }, true).then(ret => {
                                if (ret.stat) {
                                  _this.getList()
                                }
                              })
                            })
                          }
                          if(btn.label === '恢复') {
                            _tool.confirm('确定恢复票据 ？', '操作提示').then(() => {
                              _tool.post('bill/toRecover', {
                                ids: [row.id],
                                billType: row.billType,
                                menuId: _this.whichPage,
                                transType: row.transType
                              }, true).then(ret => {
                                if (ret.stat) {
                                  _this.getList()
                                }
                              })
                            })
                          }
                          if(btn.label === '打印') {
                            _tool.confirm('确定打印票据 ？', '打印提示').then(() => {
                              _tool.toPrint([row], true, false)
                            })
                          }
                        },
                      }
                    },
                    {field: 'detail', type: 'JGrid', props:{showPage:false,offsetHeight: 10, whichPage: _this.whichPage,detail: true}},
              ]
            }
            `
        },
    ]

    return ctx.suc('', tools.obj2str(ret))
})
router.post('/design/getGridRule', async ctx => {
    let obj = await tools.pageSets(ctx)
    let fields = tools.getFields(obj)
    let whichPage = ctx.request.body.whichPage?ctx.request.body.whichPage:ctx.request.body.page
    let grid = {
        field: 'grid', type: 'JGrid', props: {
            top: 50,
            showFooter: true,
            whichPage: whichPage,
            offsetHeight: '0.7',
            fields: fields.fields
        },
        inject: true,
        on: {
            cellClick: (inject, {row, column}) => {
                if(typeof hasSubDetail !== 'undefined' && hasSubDetail) {
                    if(column.property !== 'billNo') {
                        _tool.post('bill/getInvoiceDetail',{billNo: row.billNo, id: row.id}).then(ret => {
                            if(ret.stat) {
                                inject.api.mergeRule('gridDetail', {props: {dataList: ret.data}})
                            }
                        })
                    }
                }
            },
            mounted: (inject) => {
                inject.$f.exec('grid', 'getParams', inject.api, obj.listUrl)
            }
        }
    }
    return ctx.suc('suc', tools.obj2str(grid))
})


const exportOut = async (fname, data) => {
    const buffer = xlsx.build(data)
    let filename = fname + tools.dayjs().valueOf() + '.xlsx'
    await fs.writeFileSync('./download/' + filename, buffer)
    return filename
}

const combFile = async (model) => {
    let ts = `timestamps: ${!!model.sysfield},`
    let fields = ``
    let softDel = ``
    if(model.paranoid) {
        softDel = `paranoid:${!!model.paranoid},`
        ts = `timestamps: true,`
    }
    if(model.columns) {
        model.columns.map(row =>{
            let len = ``
            let defaultValue = ``
            let unique = ``
            let comment = row.comment?`comment:'${row.comment}',`: `comment:'${row.title}',`
            let pk = ``
            if(row.length) {
                let lstr = `${row.length}`
                if(row.precision) {
                    lstr += `,${row.precision}`
                }
                len = `(${lstr})`
            }
            if(row.defaultValue) {
                // defaultValue = `defaultValue:'${row.defaultValue}',`
            }
            if(row.dataType === 'UUID' && row.field==='id') {
                defaultValue = `defaultValue:fn('uuid_generate_v4'),`
            }

            let autoincr = ''
            if(row.unique) {
                unique = `unique:'${row.unique}',`
            }
            if(row.primaryKey) {
                pk = `primaryKey:${!!row.primaryKey}, `
            }
            if(row.dataType === 'autoincr') {
                row.dataType = 'INTEGER'
                autoincr = ' autoIncrement: true,'
            }
            if(row.dataType === 'BOOLEAN') row.dataType = 'TINYINT'
            fields += `${row.field}: {type:DataTypes.${row.dataType}${len},${autoincr}allowNull:${!row.notNull},${defaultValue}${unique}${comment}${pk}  },\n`
        })
    }
    // 系统字段自动加人 创建人，最后更新人
    if(model.sysfield) {
        fields += `cby: {type:DataTypes.UUID,allowNull:true,comment:'创建人'  },\n`
        fields += `uby: {type:DataTypes.UUID,allowNull:true,comment:'更新人'  },\n`
    }
    // 如果是软删除，更新删除人
    if(model.paranoid) {
        fields += `dby: {type:DataTypes.UUID,allowNull:true,comment:'删除人'  },\n`
    }

    let str = `const {DataTypes, db,fn} = require("./db");
db.define('${model.tablename}', {
    ${fields}
}, {
    tableName: '${model.tablename}',${softDel}${ts}createdAt: 'ct', updatedAt: 'ut', deletedAt: 'dt',underscored: true,comment: '${model.title}'
});`
    return str
}


















module.exports = router;
